It has been a while since I last posted a utility query, and today, to avoid other work I am supposed to be doing, I decided to go ahead and work on another post.  Today, I went ahead and worked on a server configuration type query. One query I find I use pretty often is the following one that lists the files in the database. In this blog I will include 3 queries.  The first will deal with files and databases, and the second runs in a database to see the files and their filegroups (If there is an easy way to get the filegroups at a server level, I am not sure of it…let me know).

Database Files, All Databases – File Level (Download source)

It is a pretty simple query, and it returns the following columns. (A value of ‘@TOTAL’ indicates that the row is a summary row, and some file_types will not report a file size. ):

• database_name – The name of the database
• database_file_name – The file name that was set when the file was added to the database (the logical name, not the physical name)
• size_in_kb – The size of the file in kilobytes, such that it matches the file size in the Windows Explorer
• size_in_mb – The size of the file in megabytes, a size that is more typical the people want to see
• size_in_gb – The size of the file in gigabytes, useful when looking at really large files
• file_type – How the file is used in the server
• filesystem_drive_letter – the drive letter where the file is located
• filesystem_file_name – name of the physical file
• filesystem_path – the path where the files are located.

–Get the files and total size of files for all databases

SELECT  –the name of the database
        CASE WHEN GROUPING(DB_NAME(database_id)) = 1 THEN ‘@TOTAL’
             ELSE DB_NAME(database_id)
        END AS database_name ,

        –the logical name of the file
        CASE WHEN GROUPING(master_files.name) = 1 THEN ‘@TOTAL’
             ELSE master_files.name
        END AS database_file_name ,

        –the size of the file is stored in # of pages
        SUM(master_files.size * 8.0) AS size_in_kb,
        SUM(master_files.size * 8.0) / 1024.0 AS size_in_mb,
        SUM(master_files.size * 8.0) / 1024.0 / 1024.0 AS size_in_gb,

        –the physical filename only
        CASE WHEN GROUPING(master_files.name) = 1 THEN ”
             ELSE MAX(master_files.type_desc)
        END AS file_type , 
       
        –the physical filename only
        CASE WHEN GROUPING(master_files.name) = 1 THEN ”
             ELSE MAX(UPPER(SUBSTRING(master_files.physical_name, 1, 1)))
        END AS filesystem_drive_letter ,              

       –thanks to Phillip Kelley from http://stackoverflow.com/questions/1024978/find-index-of-last-occurrence-of-a-sub-string-using-t-sql
       –for the REVERSE code to get the filename and path.

        –the physical filename only
        CASE WHEN GROUPING(master_files.name) = 1 THEN ”
             ELSE MAX(REVERSE(LEFT(REVERSE(master_files.physical_name),
                     CHARINDEX(‘\’, REVERSE(physical_name)) – 1)))
        END AS filesystem_file_name ,

        –the path of the file only
       cASE WHEN GROUPING(master_files.name) = 1 THEN ”
             ELSE MAX(REPLACE(master_files.physical_name,
                REVERSE(LEFT(REVERSE(master_files.physical_name),
                             CHARINDEX(‘\’, REVERSE(physical_name)) – 1)), ”))
             END AS filesystem_path

FROM    sys.master_files
GROUP BY DB_NAME(database_id) , –the database and filegroup and the file (all of the parts)
         master_files.name WITH rollup
ORDER BY database_name, database_file_name

Single Database By Filegroup (Download source)

Edited: Added code based on one of the comments here: http://www.sqlblog.lv/2011/05/ka-apskatit-datu-bazes-failu-izmeru-un.html.  His post does all db’s with sizing, but I preferred to have this query only work on one database. I added columns for available space, used space, as well as on disk space

In the second query, it will, for one database, list all of the row and log filegroups and their files. Like the previous query, it may list filegroups that have a 0 size for types like full text. It uses sys.database_files for the files. This has one downside, and that is that if the database is read only, it is possible that the results will not be correct and will reflect a previous version of the metadata. Use master_files if you want to get current values, but there is no guarantees that it will match the filegroups. 

It will return:

• filegroup_name – The name of the filegroup in the database
• database_file_name – The file name that was set when the file was added to the database (the logical name, not the physical name)
• size_in_kb – The size of the file in kilobytes, such that it matches the file size in the Windows Explorer
• size_in_mb – The size of the file in megabytes, a size that is more typical the people want to see (Commented Out)
• size_in_gb – The size of the file in gigabytes, useful when looking at really large files
• used_size_in_kb – The amount of the file that has data allocated, in kilobytes
• used_size_in_mb – The amount of the file that has data allocated in megabytes, a size that is more typical the people want to see (Commented Out)
• used_size_in_gb – The amount of the file that has data allocated, in gigabytes, useful when looking at really large files
• available_size_in_kb – The amount of free space in kilobytes, such that it matches the file size in the Windows Explorer
• available_size_in_mb – The amount of free space in megabytes, a size that is more typical the people want to see (Commented Out)
• available_size_in_gb – The amount of free space in gigabytes, useful when looking at really large files
• size_on_disk_kb – The amount of space the file takes in the file system (reported from the DMVs)
• file_type – How the file is used in the server
• filesystem_drive_letter – the drive letter where the file is located
• filesystem_file_name – name of the physical file
• filesystem_path – the path where the files are located.

SELECT  –the name of the database

               –the name of the filegroup (or Log for the log file, which doesn’t have a filegroup)
               CASE WHEN GROUPING(filegroups.name) = 1 THEN ‘@TOTAL’
                         WHEN filegroups.name IS NULL THEN ‘LOGS’
                         ELSE filegroups.name
                END AS filegroup_name ,
       
               –the logical name of the file
               CASE WHEN GROUPING(database_files.name) = 1 THEN ‘@TOTAL’
                        ELSE database_files.name
               END AS database_file_name ,

               –the size of the file is stored in # of pages
               SUM(database_files.size * 8.0) AS size_in_kb,
               –SUM(database_files.size * 8.0) / 1024.0 AS size_in_mb,
               SUM(database_files.size * 8.0) / 1024.0 / 1024.0 AS size_in_gb,
              
               SUM(FILEPROPERTY(database_files.NAME,’SpaceUsed’) * 8.0) AS used_size_in_kb,
               –SUM(FILEPROPERTY(database_files.NAME,’SpaceUsed’) * 8.0)/ 1024.0  AS used_size_in_mb,
               SUM(FILEPROPERTY(database_files.NAME,’SpaceUsed’) * 8.0) / 1024.0 / 1024.0 AS used_size_in_gb,                             

               SUM((database_files.size – FILEPROPERTY(database_files.NAME,’SpaceUsed’)) * 8.0) AS available_size_in_kb,
               –SUM((database_files.size – FILEPROPERTY(database_files.NAME,’SpaceUsed’)) * 8.0)/ 1024.0  AS available_size_in_mb,
               SUM((database_files.size – FILEPROPERTY(database_files.NAME,’SpaceUsed’)) * 8.0) / 1024.0 / 1024.0 AS available_size_in_gb,  

               SUM(DIVFS.size_on_disk_bytes/1024.0) AS size_on_disk_kb,
              
              –the physical filename only
              CASE WHEN GROUPING(database_files.name) = 1 THEN ”
                        ELSE MAX(database_files.type_desc)
               END AS file_type , 

               –the physical filename only
               CASE WHEN GROUPING(database_files.name) = 1 THEN ”
                        ELSE MAX(UPPER(SUBSTRING(database_files.physical_name, 1, 1)))
               END AS filesystem_drive_letter ,        
       
              –thanks to Phillip Kelley from http://stackoverflow.com/questions/1024978/find-index-of-last-occurrence-of-a-sub-string-using-t-sql

               –the physical filename only
               CASE WHEN GROUPING(database_files.name) = 1 THEN ”
                         ELSE MAX(REVERSE(LEFT(REVERSE(database_files.physical_name), CHARINDEX(‘\’, REVERSE(database_files.physical_name)) – 1)))
                END AS filesystem_file_name ,

                –the path of the file only
               CASE WHEN GROUPING(database_files.name) = 1 THEN ”
                         ELSE MAX(REPLACE(database_files.physical_name, REVERSE(LEFT(REVERSE(database_files.physical_name),
                                                                                                                   CHARINDEX(‘\’, REVERSE(database_files.physical_name)) – 1)), ”))
                END AS filesystem_path
FROM    sys.database_files –use sys.master_files if the database is read only and you want to see the metadata that is the database
             –log files do not have a filegroup
                     LEFT OUTER JOIN sys.filegroups
                             ON database_files.data_space_id = filegroups.data_space_id
                    Left Join sys.dm_io_virtual_file_stats(DB_ID(), DEFAULT) DIVFS
                            On database_files.file_id = DIVFS.file_id                        
GROUP BY  filegroups.name ,
                 database_files.name WITH ROLLUP
ORDER BY     –the name of the filegroup (or Log for the log file, which doesn’t have a filegroup)
                 CASE WHEN GROUPING(filegroups.name) = 1 THEN ‘@TOTAL’
                          WHEN filegroups.name IS NULL THEN ‘@TOTAL-SortAfter’
                          ELSE filegroups.name
                  END,
                  database_file_name

Hope these queries help out sometime.  More on the way as I finish up other projects!